
package com.sl.au.repository;

import com.sl.au.entity.Weekly;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly = true)
public interface WeeklyRepository extends JpaRepository<Weekly, String> {

	// 查看所有
	@Query(" select a from Weekly a where (a.issuer.id=?2  or (a.issuer.id != ?2 and a.published=true)) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Page<Weekly> findAllWeekly(String key, String userId, Pageable pageable);
	
	@Query(" select count(a) from Weekly a where (a.issuer.id=?2  or (a.issuer.id != ?2 and a.published=true)) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Long getCountForAll(String key, String userId);
	
	// 移动端  查看所有
	@Query(" select a from Weekly a where (a.issuer.id=?2  or (a.issuer.id != ?2 and a.published=true)) "
				+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and (a.published=?3)")
	Page<Weekly> findAllWeekly(String key, String userId,boolean published, Pageable pageable);
	@Query(" select count(a) from Weekly a where (a.issuer.id=?2  or (a.issuer.id != ?2 and a.published=true)) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and a.published=?3")
	Long getCountForAll(String key, String userId,boolean published);
	
	
	
	// 查看本部门
	@Query(" select a from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Page<Weekly> findWeekly(String key, String superior, Pageable pageable);

	@Query(" select count(a) from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Long getCountWithSuperior(String key, String superior);

	//移动端 查看本部门
	@Query(" select a from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and (a.published=?3)")
	Page<Weekly> findWeekly(String key, String superior,boolean published, Pageable pageable);
	@Query(" select count(a) from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and a.published=?3")
	Long getCountWithSuperior(String key, String superior,boolean published);

	
	
	
	
	// 查看本人
	@Query(" select a from Weekly a where a.issuer.id =?2 and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Page<Weekly> findWeeklyForMine(String key, String userId, Pageable pageable);

	@Query(" select count(a) from Weekly a where a.issuer.id =?2 "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Long getCount(String key, String userId);

	
	//移动端 查看本人
	@Query(" select a from Weekly a where a.issuer.id =?2 and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and (a.published=?3)")
	Page<Weekly> findWeeklyForMine(String key, String userId,boolean published, Pageable pageable);
	@Query(" select count(a) from Weekly a where a.issuer.id =?2 "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and a.published=?3")
	Long getCount(String key, String userId,boolean published);
	
	
	
	// 查看本片区
	@Query(" select a from Weekly a where (a.issuer.id=?3 or (a.published=true and a.issuer.id != ?3)) and (a.issuer.saleArea.id=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Page<Weekly> findWeekly(String key, String areaId, String userId, Pageable pageable);

	@Query(" select count(*) from Weekly a where (a.issuer.id=?3 or (a.published=true and a.issuer.id != ?3)) and (a.issuer.saleArea.id=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Long getCount(String key, String areaId, String userId);

	//移动端 查看本片区
	@Query(" select a from Weekly a where (a.issuer.id=?3 or (a.published=true and a.issuer.id != ?3)) and (a.issuer.saleArea.id=?2) "
				+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and (a.published=?4)")
	Page<Weekly> findWeekly(String key, String areaId, String userId,boolean published, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.issuer.id=?3 or (a.published=true and a.issuer.id != ?3)) and (a.issuer.saleArea.id=?2) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and a.published=?4")
	Long getCount(String key, String areaId, String userId,boolean published);

	
	/// 查看本科室
	@Query(" select a from Weekly a where (a.issuer.id=?3 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?2)))) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	Page<Weekly> findSectionWeekly(String key, String sectionId, String userId, Pageable pageable);

	@Query(" select count(*) from Weekly a where (a.issuer.id=?3 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?2)))) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')")
	long getSectionCount(String key, String sectionId, String userId);

	/// 移动端查看本科室
	@Query(" select a from Weekly a where (a.issuer.id=?3 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?2)))) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') and (a.published=?4)")
	Page<Weekly> findSectionWeekly(String key, String sectionId, String userId,boolean published, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.issuer.id=?3 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?2)))) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%')and a.published=?4")
	long getSectionCount(String key, String sectionId, String userId,boolean published);

	///查看所有加状态
	@Query(" select a from Weekly a where (a.issuer.id=?3  or (a.issuer.id != ?3 and a.published=true)) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	Page<Weekly> findAllWeekly(String key, String isInstructions, String userId, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.issuer.id=?3  or (a.issuer.id != ?3 and a.published=true)) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	long findAllWeeklyCount(String key, String isInstructions, String userId);

	//部门
	@Query(" select a from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?3) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	Page<Weekly> findWeekly1(String key, String isInstructions, String superior, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.published=true) and (a.issuer.saleArea.superior=?3) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	long findWeekly1Count(String key, String isInstructions, String superior);

	//本人
	@Query(" select a from Weekly a where a.issuer.id =?3 and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	Page<Weekly> findWeeklyForMine(String key, String isInstructions, String userId, Pageable pageable);
	@Query(" select count(*) from Weekly a where a.issuer.id =?3 and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	long findWeeklyForMineCount(String key, String isInstructions, String userId);

	// 查看本片区
	@Query(" select a from Weekly a where (a.issuer.id=?4 or (a.published=true and a.issuer.id != ?4)) and (a.issuer.saleArea.id=?3) "
				+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
				+ "and (a.isInstructions like '%'+?2+'%')")
	Page<Weekly> findWeekly1(String key, String isInstructions, String areaId, String userId, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.issuer.id=?4 or (a.published=true and a.issuer.id != ?4)) and (a.issuer.saleArea.id=?3) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	long findWeekly1Count(String key, String isInstructions, String areaId, String userId);

	/// 查看本科室
	@Query(" select a from Weekly a where (a.issuer.id=?4 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?3)))) "
				+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
				+ "and (a.isInstructions like '%'+?2+'%')")
	Page<Weekly> findSectionWeekly(String key, String isInstructions, String sectionId, String userId, Pageable pageable);
	@Query(" select count(*) from Weekly a where (a.issuer.id=?4 or (a.published=true and (a.issuer.saleArea.id in (select s.saleArea.id from SectionArea s where s.section.id=?3)))) "
			+ "and (a.issuer.empLogin.userName like '%'+?1+'%' or a.name like '%'+?1+'%') "
			+ "and (a.isInstructions like '%'+?2+'%')")
	long findSectionWeeklyCount(String key, String isInstructions, String sectionId, String userId);
}   